﻿using Microsoft.AspNetCore.Hosting;
using NPOI.HSSF.Record;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XWPF.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhonTai.Admin.Core.Attributes;
using ZhonTai.Admin.Tools.Cache;
using ZhonTai.Common.Extensions;
using ZhonTai.Industry.Core.Consts;
using ZhonTai.Industry.Domain.ProductionLine;
using ZhonTai.Industry.Domain.ProductModel;
using ZhonTai.Industry.Domain.WorkParamRecord;
using ZhonTai.Industry.Domain.WorkStation;
using ZhonTai.Industry.Domain.WorkStationRecord;
using ZhonTai.Industry.Repositories.WorkParamRecord;
using ZhonTai.Industry.Repositories.WorkStation;
using ZhonTai.Industry.Repositories.WorkStationRecord;
using ZhonTai.Industry.Services.WorkParamRecord.Dto.Output;
using ZhonTai.Industry.Services.WorkStationRecord.Dto.Input;
using ZhonTai.Industry.Services.WorkStationRecord.Dto.Output;
using ICell = NPOI.SS.UserModel.ICell;

namespace ZhonTai.Industry.Services.Background
{
    [InjectSingleton]
    public class BackgroundService
    {
        private readonly ICacheTool _cache;
        private readonly WorkParamRecordRepository _workParamRecordRepository;
        private readonly IWorkStationRecordRepository _workStationRecordRepository;
        private readonly WorkStationRepository _workStationRepository;
        private readonly IWebHostEnvironment env;

        public BackgroundService(IWebHostEnvironment webHostEnvironment, WorkStationRepository workStationRepository, IWorkStationRecordRepository workStationRecordRepository, WorkParamRecordRepository workParamRecordRepository, ICacheTool cache)
        {
            env = webHostEnvironment;
            _cache = cache;
            _workStationRepository = workStationRepository;
            _workStationRecordRepository = workStationRecordRepository;
            _workParamRecordRepository = workParamRecordRepository;
        }

        public async Task WriteDataExcelAsync(WorkStationExportInput input)
        {
            try
            {
                string fileName = input.FileName ?? $"ProcessData_{DateTime.Now:yyyyMMddHHmmss}.xls";
                await _cache.SetAsync(CacheKey.ExportFileName, fileName);
                string tempFilePath = Path.Combine(env.WebRootPath, "Download", "Excel", fileName);
                Directory.CreateDirectory(Path.GetDirectoryName(tempFilePath)); // 自动创建目录
                using (var fs = new FileStream(tempFilePath, FileMode.Create))
                using (var workbook = new HSSFWorkbook()) // 使用HSSF生成.xls文件（内存更优）
                {
                    // 1. 获取所有工站ID（动态过滤）
                    List<long> stationIds = _workStationRepository.Select
                        .WhereIf(input.Filter?.StationId != null && input.Filter.StationId > 0,
                            x => x.Enabled && x.Id == input.Filter.StationId)
                        .Distinct()
                        .ToList(r => r.Id);

                    int totalStations = stationIds.Count;
                    int currentStationIndex = 0;
                    foreach (var x in stationIds)
                    {
                        currentStationIndex++;
                        var stationInfo = (await _workParamRecordRepository.Select
                       .Where(w => w.StationId == x)
                       .ToListAsync(t => new
                       {
                           StationCode = t.WorkStation.StationCode,
                           StationName = t.WorkStation.StationName
                       })).LastOrDefault();
                        if (stationInfo == null)
                        {
                            continue;
                        }
                        string sheetName = $"{stationInfo.StationCode}({stationInfo.StationName})";
                        ISheet sheet = workbook.CreateSheet(sheetName);
                        var (headers, paramNames) = await BuildHeadersAsync(x);
                        WriteHeadersNpoi(sheet, headers);
                        DataTable dataTable = CreateDataTable(headers);
                        int rowIndex = 1;
                        foreach (var mounth in GetTableMounth(input.Filter))
                        {
                            // 5. 分页查询数据
                            int pageIndex = 0;
                            const int pageSize = 10000;
                            bool hasMoreData = true;
                            do
                            {
                                // 动态分表查询
                                var records = await _workStationRecordRepository.Select
                                    .AsTable((type, oldName) => GetTableName(type, mounth))
                                    .Where(r => r.StationId == x)
                                    .WhereDynamicFilter(input.DynamicFilter)
                                    .WhereIf(input.Filter?.StartTime != null,
                                        x => x.CreatedTime >= input.Filter.StartTime)
                                    .WhereIf(input.Filter?.EndTime != null,
                                        x => x.CreatedTime <= input.Filter.EndTime)
                                    .OrderByDescending(r => r.CreatedTime)
                                    .Page(pageIndex, pageSize)
                                    .ToListAsync(r => new WorkStationRecordExport
                                    {
                                        Id = r.Id,
                                        ProductCode = r.ProductCode,
                                        StationCode = r.WorkStation.StationCode,
                                        StationName = r.WorkStation.StationName,
                                        LineCode = r.WorkStation.ProductionLine.LineCode,
                                        LineName = r.WorkStation.ProductionLine.LineName,
                                        ProductModelCode = r.ProductModel.ProductCode,
                                        ProductModelName = r.ProductModel.ProductName,
                                        StartTime = r.StartTime,
                                        EndTime = r.EndTime,
                                        CycleTime = r.CycleTime,
                                        Result = r.Result,
                                        CreatedTime = r.CreatedTime
                                    });
                                if (records.Count == 0)
                                {
                                    hasMoreData = false;
                                    continue;
                                }
                                List<string?> productCodes = records
                                .Select(r => r.ProductCode)
                                .Distinct()
                                .ToList();
                                var parameters = await _workParamRecordRepository.Select
                                .AsTable((type, old) => GetTableName(type, mounth))
                                .Where(p => p.StationId == x && productCodes.Contains(p.ProductCode))
                                .ToListAsync(p => new
                                {
                                    p.ProductCode,
                                    p.ParameterName,
                                    p.ParameterValue
                                });
                                var paramDict = parameters
                                .GroupBy(p => p.ProductCode)
                                .ToDictionary(
                                     g => g.Key, // ProductCode
                                     g => g
                                         .GroupBy(p => p.ParameterName) // 按 ParameterName 分组
                                         .ToDictionary(
                                             pg => pg.Key, // ParameterName
                                             pg => pg.First().ParameterValue, // 取第一个出现的值
                                             StringComparer.OrdinalIgnoreCase // 可选：忽略大小写
                                         )
                                 );
                                foreach (WorkStationRecordExport record in records)
                                {
                                    DataRow row = dataTable.NewRow();
                                    // 固定列赋值
                                    row["过站序号"] = record.Id.ToString();
                                    row["产品编号"] = record.ProductCode;
                                    row["工站代号"] = record.StationCode;
                                    row["工站名称"] = record.StationName;
                                    row["产线代号"] = record.LineCode;
                                    row["产线名称"] = record.LineName;
                                    row["产品代号"] = record.ProductModelCode;
                                    row["产品名称"] = record.ProductModelName;
                                    row["开始时间"] = record.StartTime.ToString("yyyy-MM-dd HH:mm:ss");
                                    row["结束时间"] = record.EndTime.ToString("yyyy-MM-dd HH:mm:ss");
                                    row["循环时间"] = record.CycleTime.ToString();
                                    row["加工结果"] = record.Result;
                                    row["记录时间"] = record.CreatedTime.ToString("yyyy-MM-dd HH:mm:ss");

                                    // 动态参数列赋值
                                    foreach (string paramName in paramNames)
                                    {
                                        string value = "-";
                                        if (paramDict.TryGetValue(record.ProductCode, out var paramValues))
                                        {
                                            paramValues.TryGetValue(paramName, out value);
                                        }
                                        row[paramName] = value ?? "-";
                                    }
                                    dataTable.Rows.Add(row);
                                }
                                pageIndex++;
                                hasMoreData = records.Count >= pageSize;
                            } while (hasMoreData);
                        }
                        // 8. 批量写入NPOI Sheet
                        WriteDataTableToSheet(sheet, dataTable, ref rowIndex);
                        dataTable.Clear(); // 清空复用
                        // 9. 更新导出进度
                        double progress = Math.Round((double)currentStationIndex / totalStations * 100, 2);
                        if (progress <= 99)
                        {
                            await _cache.SetAsync(CacheKey.ExportProgress, progress);
                        }
                    }
                    // 10. 保存Excel文件
                    workbook.Write(fs);
                }
                await _cache.SetAsync(CacheKey.ExportProgress, 100);
            }
            catch (Exception ex)
            {
                await _cache.SetAsync(CacheKey.ExportProgress, 100);
            }
        }

        private async Task<(List<string> Headers, List<string> ParamNames)> BuildHeadersAsync(long stationId)
        {
            List<string> paramNames = (await _workParamRecordRepository.Select
                .Where(p => p.StationId == stationId)
                .ToListAsync(p => p.ParameterName))
                .Distinct()
                .OrderByDescending(p => p)
                .ToList();

            List<string> headers = new List<string>
        {
            "过站序号", "产品编号", "工站代号", "工站名称", "产线代号", "产线名称",
            "产品代号", "产品名称", "开始时间", "结束时间", "循环时间", "加工结果", "记录时间"
        };
            headers.AddRange(paramNames);

            return (headers, paramNames);
        }

        private DataTable CreateDataTable(List<string> headers)
        {
            DataTable dt = new DataTable();
            foreach (string header in headers)
            {
                dt.Columns.Add(header, typeof(string));
            }
            return dt;
        }

        /// <summary>
        /// 动态分表策略生成器
        /// </summary>
        private List<string> GetTableMounth(WorkStationRecordGetPageInput filter)
        {
            IEnumerable<string> GetShardingTables(DateTime start, DateTime end)
            {
                for (var month = start; month <= end; month = month.AddMonths(1))
                {
                    yield return $"{month:yyyyMM}";
                }
            }
            if (filter?.StartTime != null || filter?.EndTime != null)
            {
                var start = filter.StartTime?.Date ?? DateTime.MinValue;
                var end = filter.EndTime?.Date ?? new DateTime(9999, 12, 31);
                end = end.AddMonths(1).AddDays(-1);
                return GetShardingTables(start, end).Distinct().Reverse().ToList();
            }
            else
            {
                const int recentMonths = 1;
                var recentTables = Enumerable.Range(0, recentMonths)
                    .Select(i => DateTime.Now.AddMonths(-i))
                    .OrderByDescending(d => d) // 按时间倒序
                    .Select(d => $"{d:yyyyMM}");

                return recentTables.ToList();
            }
        }

        private string GetTableName(Type entityType, string tableMounth)
        {
            if (entityType == typeof(WorkStationRecordEntity))
                return "prd_work_station_record_" + tableMounth;
            if (entityType == typeof(WorkStationEntity))
                return "cfg_work_station";
            if (entityType == typeof(ProductionLineEntity))
                return "cfg_production_line";
            if (entityType == typeof(ProductModelEntity))
                return "prd_product_model";
            if (entityType == typeof(WorkParamRecordEntity))
                return "prd_work_param_record_" + tableMounth;
            return "prd_work_station_record_" + tableMounth;
        }

        private void WriteDataTableToSheet(ISheet sheet, DataTable dataTable, ref int startRowIndex)
        {
            foreach (DataRow dataRow in dataTable.Rows)
            {
                IRow row = sheet.CreateRow(startRowIndex++);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dataRow[i].ToString());
                }
            }
        }

        private void WriteHeadersNpoi(ISheet sheet, List<string> headers)
        {
            try
            {
                IRow headerRow = sheet.CreateRow(0);
                ICellStyle headerStyle = sheet.Workbook.CreateCellStyle();
                IFont font = sheet.Workbook.CreateFont();
                font.IsBold = true;
                headerStyle.SetFont(font);

                for (int i = 0; i < headers.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}